<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/* Start of file banks.php */
/* Location: ./cashflow/models/banks.php */
class Banks extends CI_Model {
	function __construct(){
        parent::__construct(); /** Call the Model constructor */
    }
    
    function getSQLBankBook($month,$year,$account){
        $SQL = " SELECT DISTINCT NomorBM, NomorBK, NomorCek, NomorGiro, NomorSurat, NomorRef, Tanggal";
        $SQL .=" FROM bankbook "; 
        $SQL .=" WHERE NoRekening='".$account."'";
        $SQL .=" AND Bulan='".$month."'";
        $SQL .=" AND Tahun='".$year."'";
        $SQL .=" ORDER BY Tanggal, NomorBM, NomorBK, NomorCek, NomorGiro, NomorSurat, NomorRef ";
        $q = $this->db->query($SQL);
        return $Q = $q->result_array(); 
    }
    
    function getBankBook($month,$year,$account,$order_by='yes',$no_cek='',$no_giro='',$no_surat='',$no_bm='',$no_bk=''){
        
        if($order_by=='yes')
            $this->db->select('DISTINCT Credit,Debet,NomorBM,NomorBK, NomorCek, NomorGiro, NomorSurat, NomorRef, Tanggal,Uraian,PIC,DeptId',FALSE);
        $this->db->where('NoRekening',$account,FALSE);
        $this->db->where('Bulan',$month,FALSE);
        $this->db->where('Tahun',$year,FALSE);
        
        if($no_cek)
            $this->db->where('NomorCek',$no_cek);
        if($no_giro)    
            $this->db->where('NomorGiro',$no_giro);
        if($no_surat)   
            $this->db->where('NomorSurat',$no_surat);
        if($no_bm)   
            $this->db->where('NomorBM',$no_bm);
        if($no_bk)   
            $this->db->where('NomorBK',$no_bk);
        
        if($order_by=='yes'):    
            $this->db->order_by('Tanggal');
            $this->db->order_by('NomorBM');
            $this->db->order_by('NomorBK');
            $this->db->order_by('NomorCek');
            $this->db->order_by('NomorGiro');
            $this->db->order_by('NomorSurat');
            $this->db->order_by('NomorRef');
        endif;
        $Q=$this->db->get('bankbook');    
        return $Q->result_array();
    }
    
    function sqlBankBook($month,$year,$account,$order_by='yes',$no_cek='',$no_giro='',$no_surat='',$no_bm='',$no_bk=''){
        $this->db->select('*',FALSE);
        if($no_cek)
            $this->db->where('NomorCek',$no_cek);
        if($no_giro)    
            $this->db->where('NomorGiro',$no_giro);
        if($no_surat)   
            $this->db->where('NomorSurat',$no_surat);
        if($no_bm)   
            $this->db->where('NomorBM',$no_bm);
        if($no_bk)   
            $this->db->where('NomorBK',$no_bk);
            
        $this->db->where('NoRekening',$account);
        $this->db->where('Bulan',$month);
        $this->db->where('Tahun',$year);
        return $Q=$this->db->get('bankbook');   
    }
    
    function getTotalBankBook($account,$month,$year,$no_cek='',$no_giro='',$no_surat='',$no_bm='',$no_bk=''){
        $this->db->select('SUM(Credit) as total_credit,SUM(Debet) as total_debet,COUNT(BookId) as total_data',FALSE);
        $this->db->where('NoRekening',$account);
        $this->db->where('Bulan',$month);
        $this->db->where('Tahun',$year);
        if($no_cek)
            $this->db->where('NomorCek',$no_cek);
        if($no_giro)    
            $this->db->where('NomorGiro',$no_giro);
        if($no_surat)   
            $this->db->where('NomorSurat',$no_surat);
        if($no_bm)   
            $this->db->where('NomorBM',$no_bm);
        if($no_bk)   
            $this->db->where('NomorBK',$no_bk);         
        $Q=$this->db->get('bankbook');
        return $Q->row_array();   
            
    }
        
    function getExcelSummary($month,$year){
        $accounts    =   $this->accounts->getAllAccount(); /** call a function getAllGiroes Table giro **/
        $kurs        =   $this->accounts->getConvertSaldo('USD',$month,$year); /** kurs usd to idr **/
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Summary Bank Saldo")
					->setDescription("description");
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Summary Bank Saldo');
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.99);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(100);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Account  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'BUKU BANK Summary');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+4,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+4,$row);
        
        /** first row next title page **/
        $row = 1;
        $col = 0;
        /** Account  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Rekapitulasi ');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+4,$row);
        
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Periode  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Bulan '.month_name($month).' Tahun '.$year);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+4,$row);
        
         /** row & col Column Page */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('bank')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(25); 
        
        /** coloumn Information Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('account')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(20); 
        
        /** coloumn Supplier Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('currency')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(10); 
        
        /** coloumn Dept Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('saldo')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(20); 
       
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=1;  /** no **/
        $usd = 0; /** counter total usd **/
        
        foreach($accounts as $rec):
            $saldo = $this->accounts->getAccountSaldo($rec['NoRekening'],$month,$year); /** get saldo **/
            if(($rec['MataUang']=='USD') && ($saldo['Saldo']!=0) ): /** if curr is usd and saldo not US$ 0 **/ 
                /** coloumn No **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
                /** coloumn Branch **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,$rec['Branch']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
                /** coloumn NoRekening **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,$rec['NoRekening']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
                /** coloumn Mata Uang **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,$rec['MataUang']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
                /** coloumn Saldo **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($saldo['Saldo'],2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
                $usd = $usd + $saldo['Saldo']; /** counter to Total Saldo **/ 
                $i++; /** counter to row variabel i **/ 
                $row++; /** counter to row excel **/    
            endif;
        endforeach;
        
         /** row & col Column Page */ 
        $row = $row + 0; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn Col 0 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 1 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 2 Total */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('total')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 3 USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('usd')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 4 Total USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($usd,2)); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        
         /** row & col Column Page Kurs IDR */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn Col 0 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 1 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 2 Total Konversi */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'Kurs IDR'); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 3 USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('idr')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 4 Total USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($total_idr=$usd * $kurs['Value'] ,2)); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        
        /** IDR Bank **/
        
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=$i+1;  /** no **/
        $idr = 0; /** counter total usd **/
        
        foreach($accounts as $rec):
            $saldo = $this->accounts->getAccountSaldo($rec['NoRekening'],$month,$year); /** get saldo **/
            if(($rec['MataUang']=='IDR') && ($saldo['Saldo']!=0) ): /** if curr is usd and saldo not US$ 0 **/ 
                /** coloumn No **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
                /** coloumn Branch **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,$rec['Branch']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
                /** coloumn NoRekening **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,$rec['NoRekening']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
                /** coloumn Mata Uang **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,$rec['MataUang']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
                /** coloumn Saldo **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($saldo['Saldo'],2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
                $idr = $idr + $saldo['Saldo']; /** counter to Total Saldo **/ 
                $i++; /** counter to row variabel i **/ 
                $row++; /** counter to row excel **/    
            endif;
        endforeach;
        
         /** row & col Column Page */ 
        $row = $row + 0; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn Col 0 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 1 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 2 Total */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('total')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 3 USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('idr')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 4 Total USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($idr,2)); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        
         /** row & col Column Page Kurs IDR */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn Col 0 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 1 Empty */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,''); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 2 Total Konversi */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'Total IDR'); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 3 USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('idr')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Col 4 Total USD */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($total_idr + $idr,2)); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "Bank-Summary.xls";
        $objWriter->save($file);
        return $file;
    }
    
    
    function getExcelDetails($month,$year,$acc){
        /** session variabel **/
        $month  =   $this->session->userdata('acc2_month');
        $year   =   $this->session->userdata('acc2_year'); 
        $account=   $this->session->userdata('acc2_account');
         
        $acc    = $this->accounts->getAccount($account);
        $branch = $acc['Branch'];
        $name   = $acc['NoRekening'].'('. $acc['MataUang'].')';
        
        $saldo  = $this->accounts->getAccountSaldo($account,$month,$year,'yes'); 
        $bankbooks = $this->banks->getSQLBankBook($month,$year,$account);  
        
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Detail Buku Bank")
					->setDescription("description");
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Detail-Buku-Bank');
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.99);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(70);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Buku Bank Details  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Buku Bank Detail');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+11,$row);          
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');           
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,''); 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,'');
        /** coloumn Empty **/
        
        /** first row title page **/
        $row = $row + 1;
        $col = 0;
        /** Buku Bank Details  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$branch);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+11,$row);         
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');           
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,''); 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,'');
        /** coloumn Empty **/
        
        /** first row title page **/
        $row = $row + 1;
        $col = 0;
        /** Buku Bank Details  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'No.Rekening. '.$account);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+11,$row);          
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');           
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,''); 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,'');
        /** coloumn Empty **/
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+11,$row);          
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');           
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,''); 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,'');
        /** coloumn Empty **/
        
        /** first row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Account  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Periode Bulan '.month_name($month).' '.$year);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+11,$row);          
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');           
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,''); 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,'');
        /** coloumn Empty **/
        
        
         /** row & col Column Page */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('date')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(10); 
        
        /** coloumn bm no Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('bm_no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(15); 
        
        /** coloumn bk no Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('bk_no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(15); 
        
        /** coloumn Infomation Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('information')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(35); 
       
        /** coloumn check no Col 05 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,lang('cheque_no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+5)->setWidth(15); 
        
        /** coloumn PIC Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,lang('pic')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+6)->setWidth(18); 
        
        /** coloumn Dept Col 07 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,lang('dept')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+7)->setWidth(8);
        
        /** coloumn Detail Credit Col 08 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,lang('detail_credit')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+8)->setWidth(15);
        
        /** coloumn Debet Col 09 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,lang('debet')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+9)->setWidth(15);
        
        /** coloumn Credit Col 10 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,lang('credit')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+10)->setWidth(15);
        
        /** coloumn Saldo Col 11 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,lang('saldo')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+11)->setWidth(15);
        
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i = 1; /** counter no **/
        $x = 0; /** counter to saldo **/ 
        $balance = $saldo['Saldo']; /** beginning of saldo **/
        $total_credit = 0; /** counter for total credit **/
        $total_debet  = 0; /** counter for total debet **/
        $saldo   = $balance; /** total saldo **/
        
        foreach($bankbooks as $bank):
            if($bank['NomorCek']!=""):
                $sql_sum  = $this->banks->getTotalBankBook($account,$month,$year,$bank['NomorCek']);
                $sql_data = $this->banks->sqlBankBook($month,$year,$account,'no',$bank['NomorCek']);
                $number   = $bank['NomorCek'];
                                    
            elseif($bank['NomorGiro']!=""):
                $sql_sum  = $this->banks->getTotalBankBook($account,$month,$year,'',$bank['NomorGiro']);
                $sql_data = $this->banks->sqlBankBook($month,$year,$account,'no','',$bank['NomorGiro']);
                $number   = $bank['NomorGiro'];
                                    
            elseif($bank['NomorSurat']!=""):
                $sql_sum  = $this->banks->getTotalBankBook($account,$month,$year,'','',$bank['NomorSurat']);
                $sql_data = $this->banks->sqlBankBook($month,$year,$account,'no','','',$bank['NomorSurat']);
                $number   = $bank['NomorSurat']; 
                                    
            elseif($bank['NomorBM']!=""):
                $sql_sum  = $this->banks->getTotalBankBook($account,$month,$year,'','','',$bank['NomorBM']);
                $sql_data = $this->banks->sqlBankBook($month,$year,$account,'no','','','',$bank['NomorBM']);
                $number   = "";
                                    
            elseif($bank['NomorBK']!=""):
                $sql_sum  = $this->banks->getTotalBankBook($account,$month,$year,'','','','',$bank['NomorBK']);
                $sql_data = $this->banks->sqlBankBook($month,$year,$account,'','','','','',$bank['NomorBK']);
                $number   = "";             
            endif;
            
            $value       = $sql_data->result_array();
            $credit      = $sql_sum['total_credit'];  
            $debet       = $sql_sum['total_debet'];  
            $total_data  = $sql_sum['total_data']; 
            $total_credit= $total_credit + $credit; 
            $total_debet = $total_debet + $debet;
            
            $j=1;
            foreach($value as $val):
                if($j==1)
                    $no = $number;
                else
                    $no = ""; 
                
                if($j==$total_data):
                    $pay   = decimal($credit,2);					
                    $saldo = ($saldo + $val['Debet']) - $credit;						
                else:
                    $pay   = "";					 
                    $saldo = ($saldo + $val['Debet']);
                endif;
                
                if($x==0):
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
                    
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,'');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->applyFromArray($border);
                    /** coloumn Empty **/ 
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,decimal($balance,2));
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->applyFromArray($border);
                endif;
                
                /** column No **/
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                    
                /** coloumn Date **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,indonesian_date($bank['Tanggal']));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                    
                /** coloumn Nomor BM **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,$val['NomorBM']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                    
                /** coloumn Nomor BK **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,$val['NomorBK']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                    
                /** coloumn Information **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,$val['Uraian']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setWrapText(true);
                    
                /** coloumn Nomor Cek **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,$val['NomorCek']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                    
                /** coloumn PIC **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,$val['PIC']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                    
                /** coloumn Dept **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,$val['DeptId']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                    
                /** Detail Kredit **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,decimal($val['Credit'],2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
                    
                /** coloumn Debet **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,decimal($val['Debet'],2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
                    
                /** coloumn Payment **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,$pay);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->applyFromArray($border);
                
                /** coloumn Saldo **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,decimal($saldo,2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->applyFromArray($border);
                
                $i++;
                $j++;
                $row++;
            endforeach; 
            
        endforeach;
        
         /** row & col Column Page */ 
        $row = $row + 0; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** column Title End Row **/
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Total No Rekening .'.$account);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
                    
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
                    
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
                    
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
                    
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
       
       /** Balanced Beginnning Saldo **/ 
       $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,decimal($balance,2));
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getFill()->applyFromArray($fill);
                    
       /** coloumn Total Debet **/ 
       $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,decimal($total_debet,2));
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getFill()->applyFromArray($fill);
                    
       /** coloumn Total Kredit **/ 
       $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+10,$row,decimal($total_credit,2));
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->applyFromArray($border);
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+10,$row)->getFill()->applyFromArray($fill);
                
       /** coloumn Total Saldo **/ 
       $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+11,$row,decimal($saldo,2));
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->applyFromArray($border);
       $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+11,$row)->getFill()->applyFromArray($fill);
      
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "Buku-Bank_Details.xls";
        $objWriter->save($file);
        return $file;
    }
}